import { Injectable } from "@angular/core";
import { Workbook } from "exceljs";
import * as fs from "file-saver";

@Injectable({
  providedIn: "root",
})
export class ExceljsService {
  margeLength;
  chart;
  constructor() { }

  // 人工工时
  exportExcel(excelData) {
    // Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers;
    const data = excelData.data;

    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("人工工時比例");
    // Adding Header Row
    const headerRow = worksheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    worksheet.getColumn(1).width = 20;
    worksheet.getColumn(2).width = 30;
    worksheet.getColumn(3).width = 60;
    worksheet.getColumn(4).width = 15;
    worksheet.getColumn(5).width = 15;
    worksheet.getColumn(6).width = 15;
    worksheet.getColumn(7).width = 15;
    worksheet.getColumn(8).width = 15;
    worksheet.getColumn(9).width = 15;
    worksheet.getColumn(10).width = 15;
    worksheet.getColumn(11).width = 15;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }
  // 人工工时
  exportExcel99(excelData) {
    // Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers;
    const data = excelData.data;

    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("所有工單");
    // Adding Header Row
    const headerRow = worksheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    worksheet.getColumn(1).width = 30;
    worksheet.getColumn(2).width = 20;
    worksheet.getColumn(3).width = 20;
    worksheet.getColumn(4).width = 30;
    worksheet.getColumn(5).width = 15;
    worksheet.getColumn(6).width = 50;
    worksheet.getColumn(7).width = 15;
    worksheet.getColumn(8).width = 15;
    worksheet.getColumn(9).width = 15;
    worksheet.getColumn(10).width = 15;
    worksheet.getColumn(11).width = 15;
    worksheet.getColumn(12).width = 15;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }
  // 治具校驗(總檢)
  exportExcel2(headerDatas, excelData, base64, listData) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("治具校驗(總檢)");
    // 工作簿名称 '治具校驗(總檢)'
    const title = excelData.title;
    // 表内容
    const data = excelData.data;
    // 表头
    const headerData = excelData.headers;
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 17;
      // worksheet.getRow(i).height = 20;
    }
    // 保存折线图
    const myLogoImage = workbook.addImage({
      base64: base64,
      extension: "png",
    });
    this.chart = "A9" + ":" + "O45";
    console.log(this.chart);
    worksheet.mergeCells(this.chart);
    worksheet.addImage(myLogoImage, this.chart);
    // sheet2
    const mingxiSheet = workbook.addWorksheet("總檢明細");
    // Title, Header & Data
    const zjmxTitle = listData.title;
    const zjmxHeader = listData.headers;
    const zjmxData = listData.data;
    // 表头表格合并数组
    if (listData.data.length != 0) {
      this.margeLength = zjmxData[0].length;
      console.log(this.margeLength);
      const marges = this.merges(1, 1, this.margeLength);
      this.mergeCells(mingxiSheet, marges);
      const titleRow = mingxiSheet.getCell(marges[0].split(":")[1]);
      titleRow.value = "總檢頁已品檢/已品檢NG的內容";
      titleRow.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "a1d3f5" },
      };
      titleRow.font = {
        size: 16,
        color: { argb: "000000" },
      };
      titleRow.alignment = { vertical: "middle", horizontal: "center" };
    }
    // Adding Header Row
    const headerRow = mingxiSheet.addRow(zjmxHeader);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const zjmx = [];
    zjmxData.forEach((ele) => {
      zjmx.push(mingxiSheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < zjmx.length; i++) {
      zjmx[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= zjmxHeader.length; i++) {
      mingxiSheet.getColumn(i).width = 30;
      mingxiSheet.getColumn(4).width = 50;
    }
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 治具校驗(品檢)
  exportExcel3(excelData, Pbase64, sheetdata) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    // 工作簿名称 '治具校驗(品質檢驗)'
    const worksheet = workbook.addWorksheet("治具校驗(品質檢驗)");
    const title = excelData.title; // 表内容
    const data = excelData.data; // 表头 
    this.margeLength = data[0].length;
    const marges = this.merges(1, 1, this.margeLength);
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        }; // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid", // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    } // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 15;
      // worksheet.getRow(i).height = 20;
    }
    // 保存折线图 
    // debugger
    const myLogoImage = workbook.addImage({
      base64: Pbase64,
      extension: "png",
    });
    // let num = data[0].length;
    // if (num < 8) {
    //   num = 8;
    // } else {
    // }
    this.chart = "A9" + ":" + "O45";
    console.log(this.chart);
    worksheet.mergeCells(this.chart);
    worksheet.addImage(myLogoImage, this.chart);

    // 创建新工作表
    const sheet = workbook.addWorksheet("明細説明");
    const sheetTitle = sheetdata.title;
    const sheetData = sheetdata.data;
    const header = sheetdata.headers;
    // 表头表格合并数组
    if (sheetdata.data.length != 0) {
      this.margeLength = sheetData[0].length;
      console.log(this.margeLength);
      const marges = this.merges(1, 1, this.margeLength);
      this.mergeCells(sheet, marges);
      const titleRow = sheet.getCell(marges[0].split(":")[1]);
      titleRow.value = "品檢頁已品檢/已品檢NG的內容";
      titleRow.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "a1d3f5" },
      };
      titleRow.font = {
        size: 16,
        color: { argb: "000000" },
      };
      titleRow.alignment = { vertical: "middle", horizontal: "center" };
    }
    // Adding Header Row
    const headerRow = sheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const sheet1 = [];
    sheetData.forEach((ele) => {
      sheet1.push(sheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < sheet1.length; i++) {
      sheet1[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid", // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= header.length; i++) {
      sheet.getColumn(i).width = 8;
      sheet.getColumn(1).width = 25;
      sheet.getColumn(2).width = 65;
      sheet.getColumn(4).width = 25;
      sheet.getColumn(5).width = 16;
      sheet.getColumn(8).width = 55;
      sheet.getColumn(9).width = 15;
    }
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 呆滞料
  exportExcel4(reportOrderData, excelData, base64Daizhiliao) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const ratesheet = workbook.addWorksheet("呆滯費用佔比");
    // 表内容
    const Ratedata = reportOrderData.data;
    // 设置表格内容居中对齐
    const rateList = [];
    Ratedata.forEach((ele) => {
      rateList.push(ratesheet.addRow(ele));
    });
    for (let i = 0; i < rateList.length; i++) {
      rateList[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= Ratedata[0].length; i++) {
      ratesheet.getColumn(i).width = 20;
    }
    // 保存折线图
    const myLogoImage = workbook.addImage({
      base64: base64Daizhiliao,
      extension: "png",
    });
    this.chart = "A20" + ":" + "I45";
    console.log(this.chart);
    ratesheet.mergeCells(this.chart);
    ratesheet.addImage(myLogoImage, this.chart);
    // sheet2
    const worksheet = workbook.addWorksheet("呆滯料表");
    // Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers;
    const data = excelData.data;
    // Adding Header Row
    const headerRow = worksheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    worksheet.getColumn(1).width = 20;
    worksheet.getColumn(2).width = 20;
    worksheet.getColumn(3).width = 15;
    worksheet.getColumn(4).width = 20;
    worksheet.getColumn(5).width = 15;
    worksheet.getColumn(6).width = 15;
    worksheet.getColumn(7).width = 15;
    worksheet.getColumn(8).width = 15;
    worksheet.getColumn(9).width = 30;
    worksheet.getColumn(10).width = 40;
    worksheet.getColumn(11).width = 30;
    worksheet.getColumn(12).width = 35;
    worksheet.getColumn(13).width = 25;
    worksheet.getColumn(14).width = 25;
    worksheet.getColumn(15).width = 25;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 治具OTD
  exportExcel5(headerDatas, excelData, OTDbase64) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("治具OTD");
    // 工作簿名称 '治具校驗(總檢)'
    const title = excelData.title;
    // 表内容
    const data = excelData.data;
    // 表头
    const headerData = headerDatas;
    // 表头表格合并数组
    // this.margeLength = data[0].length;
    // const marges = this.merges(1, 1, this.margeLength);
    // this.mergeCells(worksheet, marges);
    // const titleRow = worksheet.getCell(marges[0].split(':')[1]);
    // titleRow.value = '治具OTD';
    // titleRow.fill = {
    //   type: 'pattern',
    //   pattern: 'solid',
    //   fgColor: { argb: 'a1d3f5' },
    // };
    // titleRow.font = {
    //   size: 16,
    //   color: { argb: '000000' }
    // };
    // titleRow.alignment = { vertical: 'middle', horizontal: 'center' };
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 16;
      // worksheet.getRow(i).height = 20;
    }
    // 保存折线图
    const myLogoImage = workbook.addImage({
      base64: OTDbase64,
      extension: "png",
    });
    this.chart = "A7" + ":" + "I45";
    console.log(this.chart);
    worksheet.mergeCells(this.chart);
    worksheet.addImage(myLogoImage, this.chart);
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 净利润
  exportExcel6(excelData, benefitiBase64, benefitsheet2, benefitsheet3, benefitsheet4) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("淨利潤(週)");
    // // 工作簿名称 '淨利潤(週)'
    const title = excelData.title;
    // 表内容
    const data = excelData.data;
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 18;
    }
    // 保存折线图
    const myLogoImage = workbook.addImage({
      base64: benefitiBase64,
      extension: "png",
    });
    this.chart = "A10" + ":" + "I45";
    console.log(this.chart);
    worksheet.mergeCells(this.chart);
    worksheet.addImage(myLogoImage, this.chart);
    // 创建工作表 sheet2
    const sheet = workbook.addWorksheet("已經結單明細");
    const sheetData = benefitsheet2.data;
    const header = benefitsheet2.headers;
    // Adding Header Row
    const headerRow = sheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    const sheet1 = [];
    sheetData.forEach((ele) => {
      sheet1.push(sheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < sheet1.length; i++) {
      sheet1[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    for (let i = 1; i <= sheetData[0].length; i++) {
      sheet.getColumn(i).width = 30;
      sheet.getColumn(3).width = 55;
    }
    // 创建工作表 sheet3
    const sheet3 = workbook.addWorksheet("進行中單據");
    const jinxingData = benefitsheet3.data;
    const jinxingHead = benefitsheet3.headers;
    // Adding Header Row
    const jinxingHeadRow = sheet3.addRow(jinxingHead);
    jinxingHeadRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    const jixingList = [];
    jinxingData.forEach((ele) => {
      jixingList.push(sheet3.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < jixingList.length; i++) {
      jixingList[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    for (let i = 1; i <= jinxingData[0].length; i++) {
      sheet3.getColumn(i).width = 30;
      sheet3.getColumn(3).width = 55;
    }
    // // Generate & Save Excel File
    // workbook.xlsx.writeBuffer().then((result) => {
    //   const blob = new Blob([result], {
    //     type:
    //       "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    //   });
    //   fs.saveAs(blob, title + ".xlsx");
    // });
    // 创建工作表 sheet3
    const sheet32 = workbook.addWorksheet("所有單據");
    const jinxingData2 = benefitsheet4.data;
    const jinxingHead2 = benefitsheet4.headers;
    // Adding Header Row
    const jinxingHeadRow2 = sheet32.addRow(jinxingHead2);
    jinxingHeadRow2.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    const jixingList2 = [];
    jinxingData2.forEach((ele) => {
      jixingList2.push(sheet32.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < jixingList2.length; i++) {
      jixingList2[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    for (let i = 1; i <= jinxingData[0].length; i++) {
      sheet32.getColumn(i).width = 30;
      sheet32.getColumn(3).width = 55;
    }
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // delay原因分析
  exportExcel7(excelData, sheetdata) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("Delay原因分析");
    // 工作簿名称 '淨利潤(週)'
    const title = excelData.title;
    // 表内容
    const data = excelData.data;
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "left" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 16;
      worksheet.getColumn(1).width = 25;
    }
    // 创建工作表
    const sheet = workbook.addWorksheet("明細説明");
    const sheetTitle = sheetdata.title;
    const sheetData = sheetdata.data;
    const header = sheetdata.headers;
    // Adding Header Row
    const headerRow = sheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    const sheet1 = [];
    sheetData.forEach((ele) => {
      sheet1.push(sheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < sheet1.length; i++) {
      sheet1[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    sheet.getColumn(1).width = 25;
    sheet.getColumn(2).width = 25;
    sheet.getColumn(3).width = 25;
    sheet.getColumn(4).width = 25;
    sheet.getColumn(5).width = 25;
    sheet.getColumn(6).width = 25;
    sheet.getColumn(7).width = 25;
    sheet.getColumn(8).width = 25;
    sheet.getColumn(9).width = 25;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 各厂效益
  exportExcel8(excelData, xiaoyiBase64, sheetdata) {
    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("各廠效益");
    // 工作簿名称 '治具校驗(總檢)'
    const title = excelData.title;
    // 表内容
    const data = excelData.data;
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        // 设置边框粗细
        cell.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度
    for (let i = 1; i <= data[0].length; i++) {
      worksheet.getColumn(i).width = 15;
    }
    // 保存折线图
    const myLogoImage = workbook.addImage({
      base64: xiaoyiBase64,
      extension: "png",
    });
    this.chart = "A10" + ":" + "I45";
    console.log(this.chart);
    worksheet.mergeCells(this.chart);
    worksheet.addImage(myLogoImage, this.chart);
    // 创建工作表
    const sheet = workbook.addWorksheet("明細説明");
    const sheetTitle = sheetdata.title;
    const sheetData = sheetdata.data;
    const header = sheetdata.headers;
    console.log(header);
    // Adding Header Row
    const headerRow = sheet.addRow(header);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 15,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    const sheet1 = [];
    sheetData.forEach((ele) => {
      sheet1.push(sheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < sheet1.length; i++) {
      sheet1[i].eachCell((cell) => {
        cell.font = {
          size: 14,
        };
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    for (let i = 1; i <= sheetData[0].length; i++) {
      sheet.getColumn(i).width = 30;
      sheet.getColumn(3).width = 55;
    }
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 合并
  mergeCells(worksheet, marges) {
    for (const marge of marges) {
      worksheet.mergeCells(marge);
    }
  }
  /**
   * @param constArray 不需要合并的单元格数组
   * @param rowStart 从哪一行开始合并
   * @param  rowLength 合并多少行
   * @param colLength 需要合并多少列
   */
  merges(rowStart, rowLength, colLength, constArray?) {
    if (!constArray) {
      constArray = [];
    }
    let merges = constArray;
    for (let j = rowStart; j < rowLength + 1; j++) {
      for (let i = 0; i < colLength; i += this.margeLength) {
        // 有多少列需要合并
        let first;
        let second;
        let third;
        const num = constArray.length + i;
        if (num / 26 < 1) {
          first = "";
          second = String.fromCharCode(65 + (num % 26));
        } else {
          // tslint:disable-next-line: radix
          first = String.fromCharCode(65 + parseInt((num / 26).toString()) - 1);
          second = String.fromCharCode(65 + (num % 26));
        }
        const letter1 = "" + first + second + j.toString();
        if ((num + colLength - 1) % 26 === 0) {
          // tslint:disable-next-line: radix
          first = String.fromCharCode(65 + parseInt((num / 26).toString()));
        }
        if ((num + colLength - 1) / 26 < 1) {
          third = String.fromCharCode(65 + ((num + colLength - 1) % 26));
        } else {
          third = String.fromCharCode(65 + ((num + colLength - 1) % 26));
        }
        const letter2 = "" + first + third + j.toString();
        merges = [...merges, "" + letter1 + ":" + letter2];
      }
    }
    console.log(merges);
    this.chart = merges[0].split(":")[1];
    console.log("merges \n", merges);
    return merges;
  }

  exportMaterialApplyTemplate(excelData) {
    // Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers;
    const data = excelData.data;

    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("領料申請");
    // Adding Header Row
    const headerRow = worksheet.addRow(header);
    const worksheet1 = workbook.getWorksheet(1);
    console.log(worksheet1);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 12,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const contentarray = [];
    if (data) {
      data.forEach((ele) => {
        contentarray.push(worksheet.addRow(ele));
      });
    }
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度

    worksheet.getColumn(1).width = 20;
    worksheet.getColumn(2).width = 20;
    worksheet.getColumn(3).width = 25;
    worksheet.getColumn(4).width = 6;
    worksheet.getColumn(5).width = 14;
    worksheet.getColumn(6).width = 14;
    worksheet.getColumn(7).width = 28;
    worksheet.getColumn(8).width = 14;
    worksheet.getColumn(9).width = 6;
    worksheet.getColumn(10).width = 28;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }

  // 领用明细
  exportLingyong(excelData) {
    // Title, Header & Data
    const title = excelData.title;
    const header = excelData.headers;
    const data = excelData.data;

    // Create a workbook with a worksheet
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet("領用明細");
    // Adding Header Row
    const headerRow = worksheet.addRow(header);
    const worksheet1 = workbook.getWorksheet(1);
    console.log(worksheet1);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        // 设置表头背景颜色
        fgColor: { argb: "FFFFFF" },
      };
      cell.alignment = { vertical: "middle", horizontal: "center" };
      cell.font = {
        bold: true,
        // 设置表头的字体颜色
        color: { argb: "000000" },
        size: 12,
      };
      cell.border = {
        // 设置边框粗细
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    });
    // 设置表格内容居中对齐
    const contentarray = [];
    data.forEach((ele) => {
      contentarray.push(worksheet.addRow(ele));
    });
    // tslint:disable-next-line: prefer-for-of
    for (let i = 0; i < contentarray.length; i++) {
      contentarray[i].eachCell((cell) => {
        cell.border = {
          // 设置边框粗细
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        };
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          // 设置表头背景颜色
          fgColor: { argb: "FFFFFF" },
        };
        cell.alignment = { vertical: "middle", horizontal: "center" };
      });
    }
    // 设置列的宽度

    worksheet.getColumn(1).width = 10;
    worksheet.getColumn(2).width = 20;

    worksheet.getColumn(3).width = 15;
    worksheet.getColumn(4).width = 20;
    worksheet.getColumn(5).width = 42;
    worksheet.getColumn(6).width = 14;
    worksheet.getColumn(7).width = 6;
    worksheet.getColumn(8).width = 8;
    worksheet.getColumn(9).width = 65;
    worksheet.getColumn(10).width = 15;
    worksheet.getColumn(11).width = 12;
    worksheet.getColumn(12).width = 6;
    worksheet.getColumn(13).width = 6;
    worksheet.getColumn(14).width = 8;
    worksheet.getColumn(15).width = 12;
    worksheet.getColumn(16).width = 17;
    worksheet.getColumn(17).width = 40;
    // Generate & Save Excel File
    workbook.xlsx.writeBuffer().then((result) => {
      const blob = new Blob([result], {
        type:
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      });
      fs.saveAs(blob, title + ".xlsx");
    });
  }
  
}

